Spreadsheets are ubiquitous in modern business and almost all businesses rely on them for at least some of their business processes. In typical usage, what is commonly referred to as a “spreadsheet” is in fact a file (or a “workbook”) that is populated by one or more spreadsheet worksheets. The spreadsheet model and associated data will normally be distributed across multiple worksheets, and a workbook will often contain more than one model. Spreadsheet workbooks are commonly stored on disk as a file, either on the user's desktop computer or mobile device, or on a network shared folder.
A key element of any spreadsheet system is the concept of a worksheet function. All spreadsheet systems incorporate a set of standard worksheet functions (e.g. SUM, AVERAGE etc) and some systems also include the ability for a user to define their own custom functions. The most commonly used spreadsheet worksheet functions return a single value to the cell within which the function is embedded. For example, “=SUM(A1:A9)” will add all the values in cells A1 to A9 and insert their sum in the cell where the user has typed the formula. The exception to this is functions that can operate as so-called Array Functions, which is the only type where multiple values can be returned.
In the case of an array function the user selects a range of cells on the worksheet and the function populates the cells within the selected range with the different calculated values. The main disadvantage of array formulas is that the values are returned as a single block of contiguous numbers, with no indication as to what each value represents. This in turn means that the user must refer to the documentation in order to understand what each value means. In addition, the single block of contiguous numbers makes it impossible to create flexible user-interfaces—e.g. there is no way to insert cells into the array for descriptive labels.
In addition, when typing worksheet formulas into worksheet cells, existing approaches require the user to explicitly specify the cell (or range) location (or name) for each non-optional input parameter. It is not currently possible for such parameter lists to be automatically populated.